from flask import Flask, render_template, request, url_for, redirect, session
from grh_db import *
from db.dbSqlite3 import *
import openpyxl
import xlrd
from openpyxl.utils import get_column_letter
import os
import re

app = Flask(__name__)
app.secret_key = '12873%@aa%@#dx'

# 检测是否进行登录
def CheckLogin():
    if 'username' not in session:
        return False
    else:
        return True
def grhCheckLogin():
    if 'username' not in session:
        return False
    else:
        return True

# 默认初始页面为登陆界面
@app.route('/',methods=['POST','GET'])
def index():
    if request.method == "GET":
        return render_template('lxkloginStudent.html')  # 跳转至登陆界面


@app.route('/lxklogin',methods=['POST','GET'])
def lxklogin():
    if request.method == "GET":
        redirect(url_for('lxklogin'))
    result = []
    global stu_id   # 定义全局变量，获取登陆人id信息
    result, _ = GetSql2("select * from student_psw where id ='%s'" % request.form['username'])  # 查询密码权限表
    if result:
        power = result[0][2]  # 获取登陆人身份
    if not result:
        result, _ = GetSql2("select * from academic_admin_psw where id='%s'" % request.form['username'])
        if result:
            power = result[0][2]  # 获取登陆人身份
    if not result:
        fields, results = excutesql("select * from teacher_psw where id='%s'" % request.form['username'])
        result = fields
        power = results[0][2]
    print(power)
    if power == '4':        # 根据登陆人角色不同进行后续的页面跳转
        stu_id = request.form['username']   # 对全局变量进行赋值操作
        if len(result) > 0 and result[0][1] == request.form['pwd']:     # 密码对比
            session["username"] = request.form['username']              # 对比一致后将用户登录id加入session
            return redirect(url_for('lxkshow'))
        else:
            return render_template('lxkloginStudent.html')
    elif power == '3':      # 根据登陆人角色不同进行后续的页面跳转
        fields, results = excutesql("select * from teacher_psw where id='%s'" % request.form['username'])
        # print(request.form['pwd'])
        # print(request.form['username'])
        # [('2020004001', '222222', '3')]
        if len(results) > 0 and results[0][1] == request.form['pwd']:
            session["username"] = request.form['username']
            # print(111)
            return redirect(url_for('grhteacher'))
        else:
            return render_template('lxkloginStudent.html')
    elif power == '2':      # 根据登陆人角色不同进行后续的页面跳转

        if len(result) > 0 and result[0][1] == request.form['pwd']:
            session["username"] = request.form['username']
            global v
            v = request.form['username']
            return redirect(url_for('mainwx'))
        else:
            return render_template('lxkloginStudent.html')


@app.route('/lxkshow',methods=['GET','POST'])
# 初始界面
# 2020003002
def lxkshow():      # 检查登陆状态
    if not CheckLogin():
        return render_template('lxkloginStudent.html')
    strWhere = []                                   # 初始化针对性查询
    if request.method == "POST":                    # 针对于某一门成绩查询的POST请求进行处理
        if "classname" in request.form:
            name = request.form["classname"]
            if name != "":
                strWhere.append(name)               # 获取想要查询的课程名称
    message = ""
    sql = " select s.id,s.name,s.gender,s.birthday,c.name,m.name from student s,class c,major m where s.class_id = c.id and c.major_id = m.id and s.id = '%s'" % stu_id
    result, fields = GetSql2(sql)       # fields--表头名称  result--具体数据库信息
    # 改变查询结果的表头名称
    fields[0] = fields[0].replace('id','学号')
    fields[1] = fields[1].replace('name','姓名')
    fields[2] = fields[2].replace('gender','性别')
    fields[3] = fields[3].replace('birthday','出生日期')
    fields[4] = fields[4].replace('name','班级')
    fields[5] = fields[5].replace('name','专业')
    # 根据指定课程名称进行相关信息的查询
    if len(strWhere)>0:
        sql = "select st.student_id, st.course_id, st.course_num, csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {} and c.name = '{}'".format(stu_id,strWhere[0])
    else:
        sql = "select st.student_id, st.course_id, st.course_num, csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {}".format(stu_id)

    classresult, classfields = GetSql2(sql)       # fields--表头名称  result--具体数据库信息
    # fields -- 表头信息每个以字符串的形式存储于列表
    # 改变查询结果的表头名称
    classfields[0] = classfields[0].replace('student_id','学号')
    classfields[1] = classfields[1].replace('course_id', '课程号')
    classfields[2] = classfields[2].replace('course_num', '课程序号')
    classfields[3] = classfields[3].replace('semester', '开设学期')
    classfields[4] = classfields[4].replace('name', '课程名')
    classfields[5] = classfields[5].replace('course_category_id', '课程类型')
    classfields[6] = classfields[6].replace('credit', '学分')
    classfields[7] = classfields[7].replace('name', '教师')
    classfields[8] = classfields[8].replace('score', '分数')
    return render_template('lxkshowStudent.html',datas = result, fields=fields,message = message,classfields = classfields, classdatas = classresult)   # 返回渲染模板，推送数据


@app.route('/lxkmodify',methods=['GET','POST'])
# 初始界面
# 2020003002
def lxkmodify():
    if not CheckLogin():
        return render_template('lxkloginStudent.html')
    if request.method == "GET":
        # 根据GET请求进入密码修改页面，所需要的基本信息为登录者学号和姓名
        id = request.args['stu_id']
        message = ""
        sql = "select id,name from student where id = '%s'" % id
        result, fields = GetSql2(sql)
        return render_template('lxkmodifyPsw.html', message=message,data = result)
    if request.method == "POST":
        # 根据POST请求进行修改密码的一系列操作
        id = request.form['stu_id']
        sql = "select id,name from student where id = '%s'" % id
        resultbase, fieldsbase = GetSql2(sql)
        sql = "select password from student_psw where id = '%s'" % id
        result, fields = GetSql2(sql)
        oldPsw = result[0][0]                       # 获取用户原密码
        newPsw = request.form['newPsw']             # 用户新密码
        newPswAgin = request.form['newPswAgain']    # 重复输入的新密码
        message = ""                                # 存储修改密码alert信息对用户操作进行提醒
        level_middle = re.match(r'([0-9]+(\W+|\_+|[A-Za-z]+))+|([A-Za-z]+(\W+|\_+|\d+))+|((\W+|\_+)+(\d+|\w+))+',
                                newPsw)
        # 判断逻辑为：
        # 1. 新密码不可与旧密码一致
        # 2. 重复输入的新密码应与新密码一致
        if oldPsw == newPsw:
            message = "新密码与原密码一致，请重新输入更改后的密码"
            # 利用jinja语法进行用户操作合法性结果判断结果的返回
            return render_template('lxkmodifyPsw.html',message = message, data = resultbase)
        elif newPsw != newPswAgin:
            message = "两次输入的密码不一致，请更正"
            # 利用jinja语法进行用户操作合法性结果判断结果的返回
            return render_template('lxkmodifyPsw.html',message = message, data = resultbase)

        elif level_middle == None or len(newPsw)<6:
            message = "密码强度不足，请更换密码为数字字母符号的任意组合，且长度大于6位"
            return render_template('lxkmodifyPsw.html', message=message, data=resultbase)
        else:
            message = "修改成功，新密码为{}".format(newPsw)
            # 利用jinja语法进行用户操作合法性结果判断结果的返回
            sql = "update student_psw set password = '{}' where id = '{}'".format(newPsw,id)    # 执行密码表的更新操作
            execute(sql)
            sql = " select * from student_psw where id = '%s'" % id
            result, fields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            # 显示基本的用户信息和选课信息
            sql = " select s.id,s.name,s.gender,s.birthday,c.name,m.name from student s,class c,major m where s.class_id = c.id and c.major_id = m.id and s.id = '%s'" % stu_id
            result, fields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            sql = "select st.student_id, st.course_id, st.course_num,  csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {}".format(stu_id)

            classresult, classfields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            return render_template('lxkshowStudent.html', datas=result, fields=fields, message = message, classfields = classfields, classdatas = classresult)  # 返回渲染模板，推送数据


@app.route('/lxkaddclass',methods=['GET','POST'])
# 初始界面
# 2020003002
def lxkaddclass():
    if not CheckLogin():
        return render_template('lxkloginStudent.html')
    if request.method == "GET":
        # 根据GET请求进入选课页面，所需要的基本信息为登录者学号
        message = ""
        id = request.args['stu_id']
        sql_check = "select * from student_transcript where student_id = {}".format(id)
        # 查询该用户之前是否存在选课记录，用于后续的选课表查询
        result, fields = GetSql2(sql_check)
        # 该用户可以继续选课的选课表查询
        if result == []:
            sql = "select distinct pcs.course_id, csp.course_num, c.name, c.course_category_id, c.total_period, c.credit, csp.semester, csp.maximum_number, t.name, se.selected_num from plan_course_selection pcs, course_schedule_plan csp, course c, teacher t, student s, student_transcript st, selected_table se where st.course_id <> csp.course_id and t.id = csp.teacher_id and c.id = csp.course_id and pcs.course_id = csp.course_id and s.id = {} and s.class_id = pcs.class_id and se.course_id = csp.course_id and se.course_num = csp.course_num".format(id)
            result, fields = GetSql2(sql)
        else:
            sql = "select distinct csp.course_id, csp.course_num, c.name, c.course_category_id, c.total_period, c.credit, csp.semester, csp.maximum_number, t.name, se.selected_num from course_schedule_plan csp, course c, teacher t, student_transcript st, selected_table se, student s, plan_course_selection pcs where csp.course_id not in (select course_id from student_transcript where student_id = {}) and t.id = csp.teacher_id and c.id = csp.course_id and st.student_id = {} and se.course_id = csp.course_id and s.class_id = pcs.class_id and s.id = st.student_id and csp.course_id = pcs.course_id".format(id,id)
            result, fields = GetSql2(sql)
        # 改变查询结果的表头名称
        fields[0] = fields[0].replace('course_id', '课程号')
        fields[1] = fields[1].replace('course_num', '课程序号')
        fields[2] = fields[2].replace('name', '课程名')
        fields[3] = fields[3].replace('course_category_id', '课程类型')
        fields[4] = fields[4].replace('total_period', '总学时')
        fields[5] = fields[5].replace('credit', '学分')
        fields[6] = fields[6].replace('semester', '开设学期')
        fields[7] = fields[7].replace('maximum_number', '课程人数上限')
        fields[8] = fields[8].replace('name', '教师')
        fields[9] = fields[9].replace('selected_num', '已选人数')
        return render_template('lxkaddclass.html', datas = result, fields = fields, message = message, id = id)
    if request.method == "POST":
        # 根据提交结果处理批处理选课
        id = request.form['stu_id']
        message = ""
        # 批处理选课的课程学号和课程名称初始化
        course_id_list = []
        course_num_list = []
        # 添加所选课程的课程号和课程名称
        for key,value in request.form.items():
            course_id_list.append(key.split('+')[0])
            course_num_list.append(value)
        # 对列表进行过滤
        course_num_list = course_num_list[1:-1]
        course_id_list = course_id_list[1:-1]
        set_lst = set(course_id_list)
        # set会生成一个元素无序且不重复的可迭代对象
        # 批处理选课数据库操作
        if len(set_lst) == len(course_id_list):
            for i in range(len(course_id_list)):
                sql_find = "select se.selected_num, csp.maximum_number  from course_schedule_plan csp, selected_table se where se.course_id = '{}' and se.course_num = '{}' and csp.course_id = se.course_id and csp.course_num = se.course_num".format(course_id_list[i], course_num_list[i])
                num, _ = GetSql2(sql_find)
                if num[0][0]+1 <= num[0][1]:
                    sql_insert = "INSERT INTO student_transcript VALUES('{}', '{}', '{}', '--')".format(id, course_id_list[i], course_num_list[i])
                    sql_insert_up = "update selected_table set selected_num = selected_num+1 where course_id ='{}' and course_num = '{}'".format(course_id_list[i], course_num_list[i])
                    execute(sql_insert_up)
                    execute(sql_insert)
                else:
                    message = "抱歉，该课程选课人数已满"
                    sql = " select s.id,s.name,s.gender,s.birthday,c.name,m.name from student s,class c,major m where s.class_id = c.id and c.major_id = m.id and s.id = '%s'" % stu_id
                    result, fields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
                    sql = "select st.student_id, st.course_id, st.course_num,  csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {}".format(
                        stu_id)
                    # 改变表单表头
                    fields[0] = fields[0].replace('id', '学号')
                    fields[1] = fields[1].replace('name', '姓名')
                    fields[2] = fields[2].replace('gender', '性别')
                    fields[3] = fields[3].replace('birthday', '出生日期')
                    fields[4] = fields[4].replace('name', '班级')
                    fields[5] = fields[5].replace('name', '专业')
                    classresult, classfields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
                    classfields[0] = classfields[0].replace('student_id', '学号')
                    classfields[1] = classfields[1].replace('course_id', '课程号')
                    classfields[2] = classfields[2].replace('course_num', '课程序号')
                    classfields[3] = classfields[3].replace('semester', '开设学期')
                    classfields[4] = classfields[4].replace('name', '课程名')
                    classfields[5] = classfields[5].replace('course_category_id', '课程类型')
                    classfields[6] = classfields[6].replace('credit', '学分')
                    classfields[7] = classfields[7].replace('name', '教师')
                    classfields[8] = classfields[8].replace('score', '分数')
                    return render_template('lxkshowStudent.html', datas=result, fields=fields, message=message,
                                           classfields=classfields, classdatas=classresult)  # 返回渲染模板，推送数据

            message = "选课成功"
            sql = " select s.id,s.name,s.gender,s.birthday,c.name,m.name from student s,class c,major m where s.class_id = c.id and c.major_id = m.id and s.id = '%s'" % stu_id
            result, fields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            sql = "select st.student_id, st.course_id, st.course_num,  csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {}".format(stu_id)
            classresult, classfields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            return render_template('lxkshowStudent.html', datas=result, fields=fields, message=message,classfields=classfields, classdatas=classresult)  # 返回渲染模板，推送数据

        else:
            sql = "select distinct csp.course_id, csp.course_num, c.name, c.course_category_id, c.total_period, c.credit, csp.semester, csp.maximum_number, t.name, se.selected_num from course_schedule_plan csp, course c, teacher t, student_transcript st, selected_table se, student s, plan_course_selection pcs where csp.course_id not in (select course_id from student_transcript where student_id = {}) and t.id = csp.teacher_id and c.id = csp.course_id and st.student_id = {} and se.course_id = csp.course_id and s.class_id = pcs.class_id and s.id = st.student_id and csp.course_id = pcs.course_id".format(id,id)
            result, fields = GetSql2(sql)
            message = "同一类型课程只能选择一项"
            return render_template('lxkaddclass.html', datas=result, fields=fields, message=message, id=id)


@app.route('/lxkremoveclass',methods=['GET','POST'])
# 初始界面
# 2020003002
def lxkremoveclass():
    if not CheckLogin():
        return render_template('lxkloginStudent.html')
    if request.method == "GET":
        message = ""
        id = request.args['stu_id']
        sql = "select st.student_id, st.course_id, st.course_num,  csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {}".format(
            stu_id)

        classresult, classfields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
        # 改变表单表头
        classfields[0] = classfields[0].replace('student_id', '学号')
        classfields[1] = classfields[1].replace('course_id', '课程号')
        classfields[2] = classfields[2].replace('course_num', '课程序号')
        classfields[3] = classfields[3].replace('semester', '开设学期')
        classfields[4] = classfields[4].replace('name', '课程名')
        classfields[5] = classfields[5].replace('course_category_id', '课程类型')
        classfields[6] = classfields[6].replace('credit', '学分')
        classfields[7] = classfields[7].replace('name', '教师')
        classfields[8] = classfields[8].replace('score', '分数')
        return render_template('lxkremoveclass.html', datas = classresult, fields = classfields, message = message, id = id)
    if request.method == "POST":
        # 根据POST所提交的表单批处理退课操作
        id = request.form['stu_id']
        message = ""
        course_id_list = []
        course_num_list = []
        # 添加所选课程的课程号和课程名称
        for key,value in request.form.items():
            course_id_list.append(key.split('+')[0])
            course_num_list.append(value)
        # 对选课信息进行过滤
        course_num_list = course_num_list[1:-1]
        course_id_list = course_id_list[1:-1]
        # 批处理退退课操作
        if len(course_id_list) != 0:
            for i in range(len(course_id_list)):
                sql_delete = "delete from student_transcript where student_id = '{}' and course_id = '{}' and course_num = '{}'".format(id, course_id_list[i], course_num_list[i])
                sql_insert_up = "update selected_table set selected_num = selected_num-1 where course_id ='{}' and course_num = '{}'".format(course_id_list[i], course_num_list[i])
                execute(sql_insert_up)
                execute(sql_delete)
            message = "退课成功"
            sql = " select s.id,s.name,s.gender,s.birthday,c.name,m.name from student s,class c,major m where s.class_id = c.id and c.major_id = m.id and s.id = '%s'" % stu_id
            result, fields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            sql = "select st.student_id, st.course_id, st.course_num,  csp.semester, c.name, c.course_category_id, c.credit, t.name, st.score from student_transcript st, course_schedule_plan csp, course c, teacher t where st.course_id = csp.course_id and st.course_num = csp.course_num and st.course_id = c.id and csp.teacher_id = t.id and st.student_id = {}".format(stu_id)
            # 改变表单表头
            fields[0] = fields[0].replace('id', '学号')
            fields[1] = fields[1].replace('name', '姓名')
            fields[2] = fields[2].replace('gender', '性别')
            fields[3] = fields[3].replace('birthday', '出生日期')
            fields[4] = fields[4].replace('name', '班级')
            fields[5] = fields[5].replace('name', '专业')
            classresult, classfields = GetSql2(sql)  # fields--表头名称  result--具体数据库信息
            classfields[0] = classfields[0].replace('student_id', '学号')
            classfields[1] = classfields[1].replace('course_id', '课程号')
            classfields[2] = classfields[2].replace('course_num', '课程序号')
            classfields[3] = classfields[3].replace('semester', '开设学期')
            classfields[4] = classfields[4].replace('name', '课程名')
            classfields[5] = classfields[5].replace('course_category_id', '课程类型')
            classfields[6] = classfields[6].replace('credit', '学分')
            classfields[7] = classfields[7].replace('name', '教师')
            classfields[8] = classfields[8].replace('score', '分数')
            return render_template('lxkshowStudent.html', datas=result, fields=fields, message=message,classfields=classfields, classdatas=classresult)  # 返回渲染模板，推送数据

        else:
            sql = "select pcs.course_id, csp.course_num, c.name, c.course_category_id, c.total_period, c.credit, csp.semester, csp.maximum_number, t.name from plan_course_selection pcs, course_schedule_plan csp, course c, teacher t, student s where t.id = csp.teacher_id and c.id = csp.course_id and pcs.course_id = csp.course_id and s.id = {} and s.class_id = pcs.class_id".format(id)
            result, fields = GetSql2(sql)
            message = "请选择退课课程"
            return render_template('lxkremoveclass.html', datas=result, fields=fields, message=message, id=id)




@app.route('/grhteacher', methods=['GET'])
def grhteacher():
    if not grhCheckLogin():
        return redirect(url_for('grhlogin'))
    teacher_id = session['username']
    fields, results = excutesql(
        "select * from teacher,department where teacher.id='{0}' and teacher.department_id=department.id".format(
            teacher_id))
    teacher_info = results[0]
    teacher_info = [teacher_info[0], teacher_info[1], teacher_info[2], teacher_info[5]]
    print(teacher_info)

    return render_template('grh_index.html', teacher_id=teacher_id, teacher_info=teacher_info)


@app.route('/grhchangepsw', methods=['GET', 'POST'])
def change_psw():
    if not grhCheckLogin():
        return redirect(url_for('grhlogin'))
    teacher_id = session['username']
    str_info = ''
    _, teacher_info = excutesql(
        "select * from teacher,department where teacher.id='{0}' and teacher.department_id=department.id".format(
            teacher_id))
    teacher_info = teacher_info[0]
    teacher_info = [teacher_info[0], teacher_info[1], teacher_info[2], teacher_info[5]]

    print(teacher_info)

    if request.method == 'GET':
        return render_template("grh_changepsw.html", teacher_id=teacher_id, str_info=str_info,
                               teacher_info=teacher_info)
    else:
        print(request.form['oldpassword'])
        print(request.form['newpassword'])
        fields, results = excutesql("select * from teacher_psw where id='%s'" % teacher_id)
        print(results[0][1])
        if results[0][1] == request.form['oldpassword']:
            # 旧密码成功
            updatedb(
                "update teacher_psw set password='{0}' where id='{1}'".format(request.form['newpassword'], teacher_id))
            return redirect(url_for('grhteacher'))
        else:
            # 旧密码验证失败
            str_info = '旧密码错误，请重新输入'
            print('error')
            return render_template("grh_changepsw.html", teacher_id=teacher_id, str_info=str_info,
                                   teacher_info=teacher_info)
            # return redirect(url_for('change_psw'))


# 查看课程功能
@app.route('/grhcourse', methods=['GET'])
def course_information():
    if not grhCheckLogin():
        return redirect(url_for('grhlogin'))

    # print(request.form['username'])
    teacher_id = session['username']
    fields, results = excutesql(
        "select * from course_schedule_plan,course,selected_table where course_schedule_plan.course_id=selected_table.course_id and course_schedule_plan.course_num=selected_table.course_num and teacher_id='%s' and course_schedule_plan.course_id=course.id" % teacher_id)

    result_list = []
    for result in results:
        list = [result[4], result[5], result[7], result[9], result[10], result[13], result[1], result[2]]
        # print('开课信息：')
        result_list.append(list)
        # print(list)

    return render_template('grh_course.html', teacher_id=teacher_id, result_list=result_list)


@app.route('/grhcourse/grhdetails', methods=['GET'])
def course_information_details():
    if not grhCheckLogin():
        return redirect(url_for('grhlogin'))

    teacher_id = session['username']
    course_id = request.args['course_id']
    course_num = request.args['course_num']
    # print(course_id, course_num)

    fields, course_info = excutesql("select * from course where course.id='{0}'".format(course_id))
    print("-------------------------------------------")
    print(course_info[0])
    course_info = course_info[0]
    course_info = [course_info[1], course_info[3], course_info[4]]

    # 班级
    class_fields, class_results = excutesql(
        "select * from plan_course_selection where course_id='{0}' and course_num='{1}'".format(course_id,
                                                                                                course_num))
    # print(class_results)

    class_details_list = []

    for class_ in class_results:
        class_id = class_[2]
        class_categary = class_[3]
        _, class_details = excutesql(
            "select class.name,major.name,department.name from class,major,department where class.major_id=major.id and major.department_id=department.id and class.id='{0}'".format(
                class_id))
        class_details_list.append(class_details)
        # print(class_details)

    list = range(len(class_details) + 1)
    # print(list)

    # 上面为选课班级

    # 下面为选课人情况
    student_transcript_fields, student_transcript_results = excutesql(
        "select * from student_transcript where student_transcript.course_id='{0}' and student_transcript.course_num='{1}'".format(
            course_id, course_num))
    # print(student_transcript_fields, student_transcript_results)

    student_info_list = []
    for student in student_transcript_results:
        student_id = student[0]
        student_info_fields, student_info_results = excutesql(
            "select student.id,student.name,class.name from student,class where student.class_id=class.id and student.id='{0}'".format(
                student_id))
        student_info_list.append(student_info_results)
        # print(student_info_results)

    # 最大选课人数
    _, result = excutesql(
        "select * from course_schedule_plan where course_id='{0}' and course_num='{1}'".format(course_id,
                                                                                               course_num))
    max_people = result[0][4]
    # 已经选课人数
    _, result = excutesql(
        "select * from selected_table where course_id='{0}' and course_num='{1}'".format(course_id,
                                                                                         course_num))
    selected_people = result[0][2]

    # 学生名单

    student_list = openpyxl.Workbook()
    sh = student_list.active
    sh.title = '学生名单'
    sh['A1'] = '学号'
    sh['B1'] = '姓名'
    sh['C1'] = '班级'
    width = 20
    sh.column_dimensions[get_column_letter(1)].width = width
    sh.column_dimensions[get_column_letter(2)].width = width
    sh.column_dimensions[get_column_letter(3)].width = width

    # print(student_info_list)
    for i in range(len(student_info_list)):
        for j in range(len(student_info_list[i][0])):
            print(student_info_list[i][0][j])
            sh.cell(i + 2, j + 1).value = student_info_list[i][0][j]

    student_list_save_path = './static/course_details/学生名单{0}_{1}.xlsx'.format(course_id, course_num)

    student_list.save(student_list_save_path)

    student_list_save_path = '../' + student_list_save_path
    # print(student_list_save_path)

    # for i in student_info_list:
    #     print(type(i))
    #     print(i[0][1])
    # print('000000000000000000000000000000000000000000')

    return render_template('grh_course_details.html', teacher_id=teacher_id, course_id=course_id, course_num=course_num,
                           class_fields=class_fields, class_results=class_results,
                           class_details_list=class_details_list, list=list, student_info_list=student_info_list,
                           max_people=max_people, selected_people=selected_people,
                           student_list_save_path=student_list_save_path, course_info=course_info)


@app.route('/grhinputscorebyfile', methods=['GET', 'POST'])
def grhinputscorebysxlsx():
    if not grhCheckLogin():
        return redirect(url_for('grhlogin'))

    teacher_id = session['username']
    course_id = request.args['course_id']
    course_num = request.args['course_num']

    f = request.files['file']
    if f.filename == '':
        return redirect('/grhinputscore?course_id={0}&course_num={1}'.format(course_id, course_num))

    # <FileStorage: '' ('application/octet-stream')>
    # <FileStorage: '成绩2020005001_01.xlsx' ('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')>
    basepath = os.path.dirname(__file__)  # 当前文件所在路径'D:\\Data\\Pycharm Files\\web_bigwork'
    # 注意：没有的文件夹一定要先创建，不然会提示没有该路径
    # 'D:\\Data\\Pycharm Files\\web_bigwork\\static/uploads\\func.py'
    # upload_path = os.path.join(basepath, 'static/uploads',secure_filename(f.filename))
    upload_path = os.path.join(basepath, 'static/uploads', f.filename)
    f.save(upload_path)
    book = xlrd.open_workbook(upload_path)
    sheet = book.sheet_by_index(0)

    id_list = sheet.col_values(colx=0, start_rowx=1)
    score_list = sheet.col_values(colx=3, start_rowx=1)
    id_list = list(map(int, id_list))
    score_list = list(map(int, score_list))
    # print(id_list)
    # print(score_list)

    for i in range(len(id_list)):
        print(type(score_list[i]), score_list[i])

        if score_list[i] > 100 or score_list[i] < 0:
            print("error")
            return redirect('/grhinputscore?course_id={0}&course_num={1}'.format(course_id, course_num))

    for i in range(len(id_list)):
        sql = "update student_transcript set score={0} where course_id='{1}' and course_num='{2}' and student_id='{3}'".format(
            score_list[i], course_id, course_num, id_list[i])
        updatedb(sql)

    return redirect('/grhinputscore?course_id={0}&course_num={1}'.format(course_id, course_num))


@app.route('/grhinputscore', methods=['GET', 'POST'])
def grhinputscore():
    if not grhCheckLogin():
        return redirect(url_for('grhlogin'))

    teacher_id = session['username']
    course_id = request.args['course_id']
    course_num = request.args['course_num']

    if request.method == 'POST':
        # print('*' * 20)
        # print(request.form)
        id_list = []
        score_list = []

        for i, j in enumerate(request.form):
            print(i, j)
            if i % 2 == 0:
                id_list.append(request.form[j])
            else:
                score_list.append(request.form[j])

        for i in range(len(id_list)):
            sql = "update student_transcript set score={0} where course_id='{1}' and course_num='{2}' and student_id='{3}'".format(
                score_list[i], course_id, course_num, id_list[i])
            updatedb(sql)
        return redirect('/grhinputscore?course_id={0}&course_num={1}'.format(course_id, course_num))

    else:
        # 下面为选课人情况
        student_score_fields, student_score_results = excutesql(
            "select student_transcript.student_id,student.name,class.name,score from student_transcript,student,class where student_transcript.course_id='{0}' and student_transcript.course_num='{1}' and student_transcript.student_id=student.id and student.class_id=class.id".format(
                course_id, course_num))
        book = openpyxl.Workbook()
        sh = book.active

        sh.title = '学生成绩表'
        sh['A1'] = '学号'
        sh['B1'] = '姓名'
        sh['C1'] = '班级'
        sh['D1'] = '分数'

        width = 20
        sh.column_dimensions[get_column_letter(1)].width = width
        sh.column_dimensions[get_column_letter(2)].width = width
        sh.column_dimensions[get_column_letter(3)].width = width
        sh.column_dimensions[get_column_letter(4)].width = width

        for i in range(len(student_score_results)):
            for j in range(len(student_score_results[i])):
                print(student_score_results[i][j])
                sh.cell(i + 2, j + 1).value = student_score_results[i][j]
        save_path = './static/course_score/成绩{0}_{1}.xlsx'.format(course_id, course_num)
        book.save(save_path)
        # print(save_path)
        # print(student_score_fields, student_score_results)
        template_xlsx_save_path = '../' + save_path
        return render_template('grh_score.html', teacher_id=teacher_id, course_id=course_id, course_num=course_num,
                               student_score_results=student_score_results,
                               template_xlsx_save_path=template_xlsx_save_path)
# 教务管理员系统
def CheckLogin3():
    if 'username' not in session:
        return False
    else:
        return True


# 功能选择页面1
@app.route('/mainwx', methods=['GET'])
def mainwx():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    return render_template('main wx.html')


# 密码展示1
@app.route('/wx1', methods=['GET'])
def indexwx1():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))
    global n
    b, _ = GetSql2("select password from academic_admin_psw where id='%s'" % v)
    for item in b:
        for x in item:
            n = x

    return render_template('show wx1.html', v=v, n=n)


# 更新数据1
@app.route('/updatewx1', methods=['GET', 'POST'])
def upadtewx1():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        return render_template('update wx1.html', v=v, n=n)
    else:
        data1 = dict(
            id=request.form['stu_id'],
            password=request.form['stu_name'],
            authority_id=2
        )

        UpdateData(data1, "academic_admin_psw")
        return redirect(url_for("indexwx1"))


# 教师信息维护
# 2数据展示
@app.route('/wx2', methods=['GET'])
def indexwx2():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    strWhere = []
    if "name" in request.args:
        name = request.args["name"]
        if name != "":
            strWhere.append("name like '%%%s%%'" % name)
            # 模糊查询的条件

    if "stuno" in request.args:
        stuno = request.args["stuno"]
        if stuno != "":
            strWhere.append("id = '%s'" % stuno)

    tablename = "teacher"
    sql = "select * from teacher"

    if len(strWhere) > 0:
        sql = sql + " where " + " and ".join(strWhere)

    result, fields = GetSql2(sql)
    return render_template('show wx2.html', result=result, fields=fields)


# 2添加数据
@app.route('/addwx2', methods=['GET', 'POST'])
def addwx2():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        datas, _ = GetSql2("select * from department")
        return render_template('add wx2.html', datas=datas)

    else:
        data = dict(
            id=request.form['stu_id'],
            name=request.form['stu_name'],
            gender=request.form['stu_sex'],
            department_id=request.form['stu_age'])

    InsertData(data, "teacher")
    return redirect(url_for("indexwx2"))


# 2删除数据
@app.route('/delwx2/<id>', methods=['GET'])
def deletewx2(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    DelDataById("id", id, "teacher")
    # 第二个id是用户输入的
    return redirect(url_for("indexwx2"))


# 2更新数据
@app.route('/updatewx2', methods=['GET', 'POST'])
def upadtewx2():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        id = request.args['id']
        result, _ = GetSql2("select * from teacher where id='%s'" % id)
        pro, _ = GetSql2("select * from  department")

        return render_template('update wx2.html', data=result[0], pro=pro)
    else:

        data = dict(
            id=request.form['stu_id'],
            name=request.form['stu_name'],
            gender=request.form['stu_sex'],
            department_id=request.form['stu_origin']
        )
        UpdateData(data, "teacher")

        return redirect(url_for("indexwx2"))


# 2查询密码
@app.route('/chawx2/<id>', methods=['GET'])
def chawx2(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    result, fields = GetSql2("select password from teacher_psw where id='%s'" % id)
    return render_template('cha wx2.html', result=result, fields=fields, id=id)


# 2重置密码
@app.route('/resetwx2/<id>', methods=['GET'])
def resetwx2(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    data = dict(
        id=id,
        password='222222',
        authority_id=3
    )
    UpdateData(data, "teacher_psw")

    result, fields = GetSql2("select password from teacher_psw where id='%s'" % id)
    return redirect(url_for("chawx2", id=id))


# 学生系统维护
# 3数据展示
@app.route('/wx3', methods=['GET'])
def indexwx3():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    strWhere = []
    if "name" in request.args:
        name = request.args["name"]
        if name != "":
            strWhere.append("name like '%%%s%%'" % name)
            # 模糊查询的条件

    if "stuno" in request.args:
        stuno = request.args["stuno"]
        if stuno != "":
            strWhere.append("id = '%s'" % stuno)

    tablename = "student"
    sql = "select * from student"

    if len(strWhere) > 0:
        sql = sql + " where " + " and ".join(strWhere)

    result, fields = GetSql2(sql)
    return render_template('show wx3.html', result=result, fields=fields)


# 3添加数据
@app.route('/addwx3', methods=['GET', 'POST'])
def addwx3():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        datas, _ = GetSql2("select * from class")
        return render_template('add wx3.html', datas=datas)

    else:
        data = dict(
            id=request.form['stu_id'],
            name=request.form['stu_name'],
            gender=request.form['stu_sex'],
            birthday=request.form['stu_age'],
            class_id=request.form['stu_profession'])

    InsertData(data, "student")
    return redirect(url_for("indexwx3"))


# 3删除数据
@app.route('/delwx3/<id>', methods=['GET'])
def deletewx3(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    DelDataById("id", id, "student")
    # 第二个id是用户输入的
    return redirect(url_for("indexwx3"))


# 3更新数据
@app.route('/updatewx3', methods=['GET', 'POST'])
def upadtewx3():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        id = request.args['id']
        result, _ = GetSql2("select * from student where id='%s'" % id)
        pro, _ = GetSql2("select * from  class")
        return render_template('update wx3.html', data=result[0], pro=pro)
    else:

        data = dict(
            id=request.form['stu_id'],
            name=request.form['stu_name'],
            gender=request.form['stu_sex'],
            birthday=request.form['stu_age'],
            class_id=request.form['stu_origin']
        )
        UpdateData(data, "student")

    return redirect(url_for("indexwx3"))


# 3查询密码
@app.route('/chawx3/<id>', methods=['GET'])
def chawx3(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    result, fields = GetSql2("select password from student_psw where id='%s'" % id)
    return render_template('cha wx3.html', result=result, fields=fields, id=id)


# 3重置密码
@app.route('/resetwx3/<id>', methods=['GET'])
def resetwx3(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    data = dict(
        id=id,
        password='000000',
        authority_id=4
    )
    UpdateData(data, "student_psw")

    result, fields = GetSql2("select password from student_psw where id='%s'" % id)
    return redirect(url_for("chawx3", id=id))


# 课程基本信息维护
# 4数据展示
@app.route('/wx4', methods=['GET'])
def indexwx4():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    strWhere = []
    if "name" in request.args:
        name = request.args["name"]
        if name != "":
            strWhere.append("name like '%%%s%%'" % name)
            # 模糊查询的条件

    if "stuno" in request.args:
        stuno = request.args["stuno"]
        if stuno != "":
            strWhere.append("id = '%s'" % stuno)

    tablename = "course"
    sql = "select * from course"

    if len(strWhere) > 0:
        sql = sql + " where " + " and ".join(strWhere)

    result, fields = GetSql2(sql)
    return render_template('show wx4.html', result=result, fields=fields)


# 4添加数据
@app.route('/addwx4', methods=['GET', 'POST'])
def addwx4():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        datas, _ = GetSql2("select * from course_category")
        return render_template('add wx4.html', datas=datas)

    else:
        data = dict(
            id=request.form['stu_id'],
            name=request.form['stu_name'],
            course_category_id=request.form['stu_sex'],
            total_period=request.form['stu_age'],
            credit=request.form['stu_profession'])

    InsertData(data, "course")
    return redirect(url_for("indexwx4"))


# 4删除数据
@app.route('/delwx4/<id>', methods=['GET'])
def deletewx4(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    DelDataById("id", id, "course")
    # 第二个id是用户输入的
    return redirect(url_for("indexwx4"))


# 4更新数据
@app.route('/updatewx4', methods=['GET', 'POST'])
def upadtewx4():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        id = request.args['id']
        result, _ = GetSql2("select * from course where id='%s'" % id)
        pro, _ = GetSql2("select * from  course_category")
        return render_template('update wx4.html', data=result[0], pro=pro)
    else:

        data = dict(
            id=request.form['stu_id'],
            name=request.form['stu_name'],
            course_category_id=request.form['stu_sex'],
            total_period=request.form['stu_age'],
            credit=request.form['stu_origin']
        )
        UpdateData(data, "course")

    return redirect(url_for("indexwx4"))


# 排课计划信息维护
# 5数据展示
@app.route('/wx5', methods=['GET'])
def indexwx5():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    strWhere = []
    if "name" in request.args:
        name = request.args["name"]
        if name != "":
            strWhere.append("teacher_id like '%%%s%%'" % name)
            # 模糊查询的条件

    if "stuno" in request.args:
        stuno = request.args["stuno"]
        if stuno != "":
            strWhere.append("id = '%s'" % stuno)

    tablename = "course_schedule_plan"
    sql = "select * from course_schedule_plan"

    if len(strWhere) > 0:
        sql = sql + " where " + " and ".join(strWhere)

    result, fields = GetSql2(sql)
    return render_template('show wx5.html', result=result, fields=fields)


# 5添加数据
@app.route('/addwx5', methods=['GET', 'POST'])
def addwx5():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        datas, _ = GetSql2("select * from teacher")
        dataas, _ = GetSql2("select * from course")
        return render_template('add wx5.html', datas=datas, dataas=dataas)

    else:
        data = dict(
            id=request.form['stu_id'],
            course_id=request.form['stu_name'],
            course_num=request.form['stu_sex'],
            teacher_id=request.form['stu_age'],
            maximum_number=request.form['stu_profession'],
            semester=request.form['stu_aage']
        )

    InsertData(data, "course_schedule_plan")
    return redirect(url_for("indexwx5"))


# 5删除数据
@app.route('/delwx5/<id>', methods=['GET'])
def deletewx5(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    DelDataById("id", id, "course_schedule_plan")
    # 第二个id是用户输入的
    return redirect(url_for("indexwx5"))


# 5更新数据
@app.route('/updatewx5', methods=['GET', 'POST'])
def upadtewx5():
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    if request.method == "GET":
        id = request.args['id']
        result, _ = GetSql2("select * from course_schedule_plan where id='%s'" % id)
        pro, _ = GetSql2("select * from  teacher")
        prro, _ = GetSql2("select * from  course")
        return render_template('update wx5.html', data=result[0], pro=pro, prro=prro)
    else:
        data = dict(
            id=request.form['stu_id'],
            course_id=request.form['stu_name'],
            course_num=request.form['stu_sex'],
            teacher_id=request.form['stu_age'],
            maximum_number=request.form['stu_profession'],
            semester=request.form['stu_aage']
        )
        UpdateData(data, "course_schedule_plan")

    return redirect(url_for("indexwx5"))


# 3查询密码
@app.route('/chawx5/<id>', methods=['GET'])
def chawx5(id):
    if not CheckLogin3():
        return redirect(url_for('loginwx3'))

    result, fields = GetSql2("select class_id from plan_course_selection where course_id='%s'" % id)
    return render_template('cha wx5.html', result=result, fields=fields, id=id)




if __name__ == '__main__':
    app.run(debug=True)